﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using myomscl.Utils;

namespace myomscl.DataObjects
{
    public class PersonDAL : SqlBaseDal
    {
        public EstateDataSet.PersonDataTable GetPersonDetails(WorkflowFilterSql filterSql)
        {
            var result = new EstateDataSet.PersonDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_GetProfileInfo;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.UserId;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        public void UpdatePerson(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_UpdatePerson;

                    var parameters = new SqlParameter[10];
                    parameters[0] = filterSql.UserId;
                    parameters[1] = filterSql.FirstName;
                    parameters[2] = filterSql.LastName;
                    parameters[3] = filterSql.Phone1;
                    parameters[4] = filterSql.Phone2;
                    parameters[5] = filterSql.Email;
                    parameters[6] = filterSql.Login;
                    parameters[7] = filterSql.Password;
                    parameters[8] = filterSql.Confirmation;
                    parameters[9] = filterSql.Notification;

                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        public void CreateNetworkOperatorPerson(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_NetworkOperator_AddPerson;

                    var parameters = new SqlParameter[10];
                    parameters[0] = filterSql.FirstName;
                    parameters[1] = filterSql.LastName;
                    parameters[2] = filterSql.Phone1;
                    parameters[3] = filterSql.Phone2;
                    parameters[4] = filterSql.Email;
                    parameters[5] = filterSql.Login;
                    parameters[6] = filterSql.Password;
                    parameters[7] = filterSql.Confirmation;
                    parameters[8] = filterSql.Notification;
                    parameters[9] = filterSql.NetworkOperatorId;


                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        public DataTable GetNetworkOperatorPersonList(WorkflowFilterSql filterSql, Boolean showdisabled)
        {
            var result = new DataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_NetworkOperator_GetPersonList;

                    var parameters = new SqlParameter[2];
                    parameters[0] = filterSql.NetworkOperatorId;
                    parameters[1] = new SqlParameter
                {
                    ParameterName = Constants.Sp_Param_ShowDisabled,
                    Value = showdisabled,
                    IsNullable = false,
                    SqlDbType = SqlDbType.Bit,
                    Direction = ParameterDirection.Input
                };

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        public bool ValidateUser(WorkflowFilterSql filterSql, ref string exceptionField)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_User_Validate;

                    var parameters = new SqlParameter[7];
                    parameters[0] = filterSql.Login;
                    parameters[1] = filterSql.Email;
                    parameters[2] = filterSql.NetworkOperatorId;
                    parameters[3] = filterSql.ContractorId;
                    parameters[4] = filterSql.UserId;

                    parameters[5] = new SqlParameter
                    {
                        ParameterName = "@exceptionfield",
                        Direction = ParameterDirection.Output,
                        SqlDbType = SqlDbType.NVarChar,
                        Size = 15
                    };

                    parameters[6] = new SqlParameter
                    {
                        Direction = ParameterDirection.ReturnValue,
                        SqlDbType = SqlDbType.Bit
                    };

                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                    if (cmd.Parameters[6].Value.ToString() == "0")
                    {
                        exceptionField = cmd.Parameters[5].Value.ToString();
                        return false;
                    }
                    return true;
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        public string GetPasswordByEmail(string email)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_User_GetByEmail;

                    var parameters = new SqlParameter[1];
                    parameters[0] = new SqlParameter
                    {
                        ParameterName = Constants.Sp_Param_Email,
                        Value = email,
                        IsNullable = false,
                        SqlDbType = SqlDbType.VarChar,
                        Direction = ParameterDirection.Input
                    };

                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    return (string)cmd.ExecuteScalar();

                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }
    }
}
